package org.posupe.curriculos;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.posupe.bdr.PersistenceMechanismException;
import org.posupe.bdr.PersistenceMechanismRDBMS;
import org.posupe.candidatos.CandidatoInexistenteException;
import org.posupe.candidatos.RepositorioCandidatosBDR;
import org.posupe.cursos.Curso;
import org.posupe.cursos.IteratorCursos;
import org.posupe.cursos.IteratorCursosVector;
import org.posupe.util.RepositorioException;

public class RepositorioCurriculosCursosBDR {

	private static RepositorioCurriculosCursosBDR instance;
		
	private PersistenceMechanismRDBMS pm;
	
	private RepositorioCurriculosCursosBDR() throws RepositorioException {
		try {
			this.pm = PersistenceMechanismRDBMS.getInstance();
			this.pm.connect();
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} 
	}
	
	public static RepositorioCurriculosCursosBDR getInstance() throws RepositorioException {
		if (instance == null) {
			instance = new RepositorioCurriculosCursosBDR();
		}
		return instance;
	}
	
	public void associar(int codigoCurriculo, int codigoCurso) throws RepositorioException {
		String sql = "INSERT INTO CURRICULO_CURSO(COD_CURRICULO, COD_CURSO) VALUES (?,?)";
		PreparedStatement statement;
		try {
			statement = (PreparedStatement) pm.getCommunicationChannel(sql);
			statement.setInt(1, codigoCurriculo);
			statement.setInt(2, codigoCurso);
			int quantidade = statement.executeUpdate(sql);
			if (quantidade == 0){
				throw new RepositorioException(new Throwable("Erro ao associar curriculo ao curso."));
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		}
		
    
	}

	public void desassociar(int codigoCurriculo, int codigoCurso) throws RepositorioException {
		String sql = "DELETE FROM CURRICULO_CURSO WHERE COD_CURRICULO = ? AND COD_CURSO = ?";
		PreparedStatement statement;
		try {
			statement = (PreparedStatement) pm.getCommunicationChannel(sql);
			statement.setInt(1, codigoCurriculo);
			statement.setInt(2, codigoCurso);
			int quantidade = statement.executeUpdate(sql);
			if (quantidade == 0){
				throw new RepositorioException(new Throwable("Erro ao desassociar curriculo ao curso."));
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		}
	}

	public boolean isAssociado(int codigoCurriculo, int codigoCurso) throws RepositorioException {
		String sql = "SELECT * FROM CURRICULO_CURSO WHERE COD_CURRICULO = ? AND COD_CURSO = ?";
		PreparedStatement statement;
		boolean resultado = false;
		try {
			statement = (PreparedStatement) pm.getCommunicationChannel(sql);
			statement.setInt(1, codigoCurriculo);
			statement.setInt(2, codigoCurso);
			ResultSet resultset = statement.executeQuery();
			resultado = resultset.next(); 
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		}
		return resultado;
	}
	public IteratorCurriculos getCurriculosAssociados(int codigoCurso) throws RepositorioException, CandidatoInexistenteException {
		String sql = "SELECT * FROM CURRICULO WHERE COD_CURRICULO IN (SELECT COD_CURRICULO FROM CURRICULO_CURSO WHERE COD_CURSO = ?)";
		PreparedStatement statement;
		IteratorCurriculosVector iteratorCurriculos = null;
		Curriculo c = null;
		try {
			statement = (PreparedStatement) pm.getCommunicationChannel(sql);
			statement.setInt(1, codigoCurso);
			ResultSet resultset = statement.executeQuery();
			iteratorCurriculos = new IteratorCurriculosVector();

			while (resultset.next()){
				c = new Curriculo(resultset.getInt("CODIGO"), 
						RepositorioCandidatosBDR.getInstance().procurar(resultset.getString("COD_CANDIDATO")),
						resultset.getDate("DATA_CADASTRO"));
				iteratorCurriculos.add(c);
			}
			if (!iteratorCurriculos.hasNext()){
				throw new RepositorioException(new Throwable("Nenhum curriculo associado ao curso."));
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		}
		return iteratorCurriculos;
	}	
	public IteratorCursos getCursosAssociados(int codigoCurriculo) throws RepositorioException {
		String sql = "SELECT * FROM CURSO WHERE COD_CURSO IN (SELECT COD_CURSO FROM CURRICULO_CURSO WHERE COD_CURRICULO = ?)";
		PreparedStatement statement;
		IteratorCursosVector iteratorCursos = null;
		Curso c = null;
		try {
			statement = (PreparedStatement) pm.getCommunicationChannel(sql);
			statement.setInt(1, codigoCurriculo);
			ResultSet resultset = statement.executeQuery();
			iteratorCursos = new IteratorCursosVector();

			while (resultset.next()){
				c = new Curso(resultset.getInt("CODIGO"),
        				  resultset.getString("NOME"),
        				  resultset.getString("DESCRICAO"),
        		          resultset.getString("NIVEL"),
        		          null, //TODO RepositorioInstituicaoEnsino.getInstance().procurar(resultset.getString("COD_INSTITUICAO");
        		          resultset.getInt("CARGA_HORARIA"));
	            iteratorCursos.add(c);
			}
			if (!iteratorCursos.hasNext()){
				throw new RepositorioException(new Throwable("Nenhum curso associado ao curriculo."));
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		}
		return iteratorCursos;
	}
	
	
}
